const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
const crypto = require('crypto');
const config = require('../config');

// 创建数据库连接池
const pool = mysql.createPool({
    host: process.env.DB_HOST || config.db.host,
    user: process.env.DB_USER || config.db.user,
    password: process.env.DB_PASSWORD || config.db.password,
    database: process.env.DB_NAME || config.db.database,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 辅助函数：计算字符串的sha256哈希
function sha256(input) {
    return crypto.createHash('sha256').update(input).digest('hex');
}

// 查找用户（按用户名和角色）
const findUserByUsernameAndRole = async (username, role) => {
    try {
        let query, values;
        
        if (role === 'student') {
            // 学生查询
            query = `
                SELECT 
                    id, 
                    s_no AS username, 
                    password, 
                    'student' AS role,
                    s_name AS name,
                    email,
                    TRUE AS isActive
                FROM students 
                WHERE s_no = ? AND ? = 'student'
            `;
            values = [username, role];
        } else {
            // 教师/管理员查询
            query = `
                SELECT 
                    id, 
                    employee_no AS username, 
                    password, 
                    IF(is_admin, 'admin', 'teacher') AS role,
                    name,
                    email,
                    TRUE AS isActive
                FROM teachers 
                WHERE employee_no = ? AND ? IN ('teacher', 'admin')
            `;
            values = [username, role];
        }

        const [rows] = await pool.query(query, values);
        const user = rows[0];
        
        // 对于管理员角色，需验证is_admin=true
        if (user && role === 'admin' && user.role !== 'admin') {
            return null;
        }
        
        return user || null;
    } catch (error) {
        console.error('Database query error:', error);
        throw error;
    }
};

// 查找学生
const findStudentById = async (id) => {
    try {
        const student = await pool.query(`
            SELECT 
                id, 
                s_no AS username, 
                password, 
                'student' AS role,
                s_name AS name,
                email
            FROM students 
            WHERE id = ?
        `, [id]);
    } catch (error) {
        console.error('Database query error:', error);
        throw error;
    }
};


const findUserById = async (id) => {
    try {
        // 先查教师表
        const [teacherRows] = await pool.query(`
            SELECT 
                id, 
                employee_no AS username, 
                password, 
                IF(is_admin, 'admin', 'teacher') AS role,
                name,
                email,
                TRUE AS isActive
            FROM teachers 
            WHERE id = ?
        `, [id]);
        
        if (teacherRows.length > 0) {
            return teacherRows[0];
        }
        
        // 再查学生表
        const [studentRows] = await pool.query(`
            SELECT 
                id, 
                s_no AS username, 
                password, 
                'student' AS role,
                s_name AS name,
                email,
                TRUE AS isActive
            FROM students 
            WHERE id = ?
        `, [id]);
        
        return studentRows[0] || null;
    } catch (error) {
        console.error('Database query error:', error);
        throw error;
    }
};

// 验证密码（保持不变）
const verifyPassword = async (user, inputPassword) => {
    // 注意：inputPassword是前端已经sha256加密过的字符串
    return bcrypt.compareSync(inputPassword, user.password);
};

module.exports = {
    findUserById,
    findUserByUsernameAndRole,
    verifyPassword
};